/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseproject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rohit Chand
 */
public class Interface  {
  private static final String URL = "jdbc:derby://localhost:1527/ist411";
   private static final String USERNAME = "rohit";
   private static final String PASSWORD = "";
   private Connection con;
   public int addressCounter = 0;
   String addressName ;
   public int orderCounter = 0;
   
   
   
public Interface () throws SQLException 
{
    
      con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}//end Constructor Interface() 

// This method saves shipping data in the database 
public List<Products> getAllSoftware() throws SQLException{
       
 List< Products > results = null;
      ResultSet resultSet = null;
      
      try 
      {
        Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
         resultSet = stmt.executeQuery("SELECT * FROM Products WHERE catalog_id = 1");
         results = new ArrayList<  >();
         
         while ( resultSet.next() )
         {
            results.add( new Products(
               resultSet.getString( "name" ),
               resultSet.getString( "Image_Link" ),
               resultSet.getString( "Price" ),
               resultSet.getString( "description" ),
               resultSet.getInt( "catalog_id" ),
               resultSet.getInt( "product_id" )));
         } // end while
      } // end try
      catch ( SQLException sqlException )
      {
         sqlException.printStackTrace();         
      } // end catch
      finally
      {
         try 
         {
            resultSet.close();
         } // end try
         catch ( SQLException sqlException )
         {
            sqlException.printStackTrace();         
            con.close();
         } // end catch
      } // end finally
      
      return results;
   } // end method getAllSoftware 
        
public List<Products> getAllHardware() throws SQLException{
       
 List< Products > results = null;
      ResultSet resultSet = null;
      
      try 
      {
        Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
         resultSet = stmt.executeQuery("SELECT * FROM Products WHERE catalog_id = 2");
         results = new ArrayList<  >();
         
         while ( resultSet.next() )
         {
            results.add( new Products(
               resultSet.getString( "name" ),
               resultSet.getString( "Image_Link" ),
               resultSet.getString( "Price" ),
               resultSet.getString( "description" ),
               resultSet.getInt( "catalog_id" ),
               resultSet.getInt( "product_id" )));
         } // end while
      } // end try
      catch ( SQLException sqlException )
      {
         sqlException.printStackTrace();         
      } // end catch
      finally
      {
         try 
         {
            resultSet.close();
         } // end try
         catch ( SQLException sqlException )
         {
            sqlException.printStackTrace();         
            con.close();
         } // end catch
      } // end finally
      
      return results;
   } // end method getAllSoftware 


public List<Address> getAddress() throws SQLException{
       
 List< Address > results = null;
      ResultSet resultSet = null;
      
      try 
      {
        Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
         resultSet = stmt.executeQuery("SELECT * FROM address");
         results = new ArrayList<  >();
         
         while ( resultSet.next() )
         {
            results.add( new Address(
               resultSet.getInt( "Address_Id" ),
               resultSet.getString( "Address" ),
               resultSet.getString( "City" ),
               resultSet.getString( "State" ),
               resultSet.getString( "Zip" ),
               resultSet.getString( "Name" ),
               resultSet.getString("Email")
                    )
                    );
            
            
             
         } // end while
      } // end try
      catch ( SQLException sqlException )
      {
         sqlException.printStackTrace();         
      } // end catch
      finally
      {
         try 
         {
            resultSet.close();
         } // end try
         catch ( SQLException sqlException )
         {
            sqlException.printStackTrace();         
            con.close();
         } // end catch
      } // end finally
      
      return results;
   } // end method getAllSoftware 

public void insertAddress(String Address, String City, String State, String Zip, String Name, String Email)
{
    addressCounter++;
    String address = Address;
    String city = City;
    String state = State; 
    String zip = Zip; 
     addressName = Name;
     String email = Email;
        try {
            Statement stmt = con.createStatement();
            
            stmt.executeUpdate("insert into app.Address values ("+addressCounter+", '"+address+"', '"+city+"', '"+state+"', '"+zip+"', '"+addressName+"', '"+Email+"')");
            
        } catch (SQLException ex) {
            Logger.getLogger(Interface.class.getName()).log(Level.SEVERE, null, ex);
        }
}//end insertAddress



public void insertOrders(int TotalPrice)
{
    orderCounter++;
    int totalPrice = TotalPrice;
        try {
            Statement stmt = con.createStatement();
            
            stmt.executeUpdate("insert into app.orders values ("+orderCounter+", "+addressCounter+", "+totalPrice+")");
            
            
        } catch (SQLException ex) {
            Logger.getLogger(Interface.class.getName()).log(Level.SEVERE, null, ex);
        }
    
}//end insertOrders()


public void insertPruchasedItem(int Product_id, int Quantity){
    
   int product_id = Product_id;
   int quantity = Quantity;
        try {
            Statement stmt = con.createStatement();
            
            stmt.executeQuery("insert into app.purchaseditem values ("+product_id+", "+quantity+", "+orderCounter+")");
            
            
        } catch (SQLException ex) {
            Logger.getLogger(Interface.class.getName()).log(Level.SEVERE, null, ex);
        }
}




}//end Class Interface 
